﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.DAL;
using Automobile.service.Model;
using Automobile.service.Common;
using System.Data;
using System.Data.SqlClient;
namespace Automobile.service.DAL
{
   public class MemberCardDAL
    {
       SQLHelper helper = new SQLHelper();
      
       /// <summary>
       /// 获取会员记录总行数
       /// </summary>
       /// <param name="index"></param>
       /// <param name="size"></param>
       /// <returns></returns>
       public int CountMemberCard(string firstdate, string lastdate, string MemberCard_owner)
       {
           StringBuilder bulider = new StringBuilder();
           bulider.Append("select count(*) from MemberCard where 1=1");
           if (firstdate != "" && lastdate != "")
           {
               bulider.Append(" and MemberCard_Adddate between '" + firstdate + "' and  '" + lastdate + " 12:00'");
           }
           if (MemberCard_owner != "")
           {
               bulider.Append(" and MemberCard_owner like '%" + MemberCard_owner + "%'");
           }
           return Convert.ToInt32(helper.ExecScalar(bulider.ToString()));
       }
       /// <summary>
       /// 删除会员信息
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public int DelMemberCard(string id)
       {
           StringBuilder builder = new StringBuilder();
           SqlParameter[] par = { 
                                new SqlParameter("@str",SqlDbType.VarChar,1000)
                                };
           par[0].Value = id;
           return helper.ExecNonquery_Proc("Proc_DelMemberCard", par);
       }
       
       /// <summary>
       /// 添加会员信息
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public int AddMemberCard(MemberCard m)
       {
           StringBuilder bulider = new StringBuilder();
           bulider.Append("insert into MemberCard values(@MemberCard_Name,@MemberCard_owner,@MemberCard_Cnum,@MemberCard_CardID,@MemberCard_ingeral,@MemberCard_sphere,@MemberCardRank_Rank,@MemberCard_State,getdate())");
           SqlParameter[] par ={
                          new SqlParameter("@MemberCard_Name",SqlDbType.VarChar,50),
                          new SqlParameter("@MemberCard_owner",SqlDbType.VarChar,50),
                          new SqlParameter("@MemberCard_Cnum",SqlDbType.VarChar,50),
                          new SqlParameter("@MemberCard_CardID",SqlDbType.VarChar,50),
                          new SqlParameter("@MemberCard_ingeral",SqlDbType.VarChar,50),
                          new SqlParameter("@MemberCard_sphere",SqlDbType.VarChar,50),
                          new SqlParameter("@MemberCardRank_Rank",SqlDbType.VarChar,50),
                          new SqlParameter("@MemberCard_State",SqlDbType.VarChar,50)
                              };
           par[0].Value=m.MemberCard_Name;
           par[1].Value=m.MemberCard_owner;
           par[2].Value=m.MemberCard_Cnum;
           par[3].Value=m.MemberCard_CardID;
           par[4].Value=m.MemberCard_ingeral;
           par[5].Value=m.MemberCard_sphere;
           par[6].Value=m.MemberCardRank_Rank;
           par[7].Value = m.MemberCard_State;
           return helper.ExecNonquery(bulider.ToString(), par);
       }
       
       /// <summary>
       /// 分页显示会员信息(查询)
       /// </summary>
       /// <param name="index"></param>
       /// <param name="size"></param>
       /// <returns></returns>
       public DataTable SearchMemberCard(int index, int size, string firstdate, string lastdate, string MemberCard_owner)
       {
           StringBuilder bulider = new StringBuilder();
           bulider.Append("select top " + size + " * from (select ROW_NUMBER() over(order by MemberCard_Id) nid,* from MemberCard)as tb where nid>(" + index + "-1)*" + size + "");
           if (firstdate != "" && lastdate != "")
           {
               bulider.Append(" and MemberCard_Adddate between '" + firstdate + "' and  '" + lastdate + " 24:00'");
           }
           if (MemberCard_owner!="")
           {
               bulider.Append(" and MemberCard_owner like '%"+MemberCard_owner+"%'");
           }
           return helper.ExecDataTable(bulider.ToString());
       }
       /// <summary>
       /// 会员信息填充下拉框
       /// </summary>
       /// <returns></returns>
       public DataTable ShowMenberCard()
       {
           string sql = "select MemberCard_Id,MemberCard_Name from MemberCard";
           return helper.ExecDataTable(sql);
       }
       /// <summary>
       /// 根据id获取会员信息
       /// </summary>
       /// <param name="index"></param>
       /// <param name="size"></param>
       /// <returns></returns>
       public DataTable GetMemberCardByID(int id)
       {
           StringBuilder builder = new StringBuilder();
           builder.Append(" select * from MemberCard where MemberCard_Id=@id");
           SqlParameter[] par = {
                                new SqlParameter("@id",SqlDbType.Int)
                                };
           par[0].Value = id;
           return helper.ExecDataTable(builder.ToString(),par);
       }
       /// <summary>
       /// 修改会员信息
       /// </summary>
       /// <param name="index"></param>
       /// <param name="size"></param>
       /// <returns></returns>
       public int UptMemberCardByID(MemberCard m)
       {
           StringBuilder builder = new StringBuilder();
           builder.Append("update MemberCard set MemberCard_Name=@MemberCard_Name,MemberCard_owner=@MemberCard_owner,MemberCard_Cnum=@MemberCard_Cnum,MemberCard_CardID=@MemberCard_CardID,MemberCard_ingeral=@MemberCard_ingeral,MemberCard_sphere=@MemberCard_sphere,MemberCardRank_Rank=@MemberCardRank_Rank where MemberCard_Id=@MemberCard_Id");
           SqlParameter[] par = {
                                new SqlParameter("@MemberCard_Name",SqlDbType.Int),
                                new SqlParameter("@MemberCard_owner",SqlDbType.VarChar,50),
                                new SqlParameter("@MemberCard_Cnum",SqlDbType.VarChar,50),
                                new SqlParameter("@MemberCard_CardID",SqlDbType.VarChar,50),
                                new SqlParameter("@MemberCard_ingeral",SqlDbType.VarChar,50),
                                new SqlParameter("@MemberCard_sphere",SqlDbType.VarChar,50),
                                new SqlParameter("@MemberCardRank_Rank",SqlDbType.VarChar,50),
                                new SqlParameter("@MemberCard_Id",SqlDbType.VarChar,50),
                                };
           par[0].Value = m.MemberCard_Name;
           par[1].Value = m.MemberCard_owner;
           par[2].Value = m.MemberCard_Cnum;
           par[3].Value = m.MemberCard_CardID;
           par[4].Value = m.MemberCard_ingeral;
           par[5].Value = m.MemberCard_sphere;
           par[6].Value = m.MemberCardRank_Rank;
           par[7].Value = m.MemberCard_Id;
           return helper.ExecNonquery(builder.ToString(), par);
       }
       
       /// <summary>
       /// 修改状态会员
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public int UptMemberCard_StateByID(int MemberCard_Id, int MemberCard_State)
       {
           StringBuilder builder = new StringBuilder("update MemberCard set MemberCard_State=@MemberCard_State where MemberCard_Id=@MemberCard_Id");
           SqlParameter[] par = { 
                                new SqlParameter("@MemberCard_State",SqlDbType.Int,4),
                                new SqlParameter("@MemberCard_Id",SqlDbType.Int,4),
                                };
           par[0].Value = MemberCard_State;
           par[1].Value = MemberCard_Id;
           return helper.ExecNonquery(builder.ToString(), par);
       }
       
    }
}
